﻿Public Class clsTrSzukanie

#Region "Attributes"

    Private mvarTrZlecenia As Collection(Of clsInhTrZlecenie)
    Private mvarSchematTab As New DataTable
    Private mvarTrSchemat As Collection(Of clsInhTrSchemat)
    Private mvarTrPozycjaSchematu As Collection(Of clsInhTrPozycjeSchematu)
    Private mvarTrDrut As Collection(Of clsInhTrDrut)
    Private mvarTrMaterialProdukcyjny As Collection(Of clsInhTrMaterialProdukcyjny)
    Private mvarPozycjeSchematuIdTab As New DataTable
    Private mvarZleceniaTab As New DataTable
    Private mvarWyznDrut As Boolean  'wyznaczynik informuje czy do danej pozycji jest drut do kontroli
    Private mvarWagaDrutu As Double
    Private mvarDrutTab As New DataTable
#End Region

#Region "Properties"

    Public Property Zlecenia() As Collection(Of clsInhTrZlecenie)
        Get
            Return mvarTrZlecenia
        End Get
        Set(ByVal value As Collection(Of clsInhTrZlecenie))
            mvarTrZlecenia = value
        End Set
    End Property

    Public Property SchematTab() As DataTable
        Get
            Return mvarSchematTab
        End Get
        Set(ByVal value As DataTable)
            mvarSchematTab = value
        End Set
    End Property

    Public Property Schemat() As Collection(Of clsInhTrSchemat)
        Get
            Return mvarTrSchemat
        End Get
        Set(ByVal value As Collection(Of clsInhTrSchemat))
            mvarTrSchemat = value
        End Set
    End Property

    Public Property PozycjaSchematu() As Collection(Of clsInhTrPozycjeSchematu)
        Get
            Return mvarTrPozycjaSchematu
        End Get
        Set(ByVal value As Collection(Of clsInhTrPozycjeSchematu))
            mvarTrPozycjaSchematu = value
        End Set
    End Property

    Public Property Drut() As Collection(Of clsInhTrDrut)
        Get
            Return mvarTrDrut
        End Get
        Set(ByVal value As Collection(Of clsInhTrDrut))
            mvarTrDrut = value
        End Set
    End Property

    Public Property MaterialProdukcyjny() As Collection(Of clsInhTrMaterialProdukcyjny)
        Get
            Return mvarTrMaterialProdukcyjny
        End Get
        Set(ByVal value As Collection(Of clsInhTrMaterialProdukcyjny))
            mvarTrMaterialProdukcyjny = value
        End Set
    End Property

    Public Property PozycjeSchematuIdTab() As DataTable
        Get
            Return mvarPozycjeSchematuIdTab
        End Get
        Set(ByVal value As DataTable)
            mvarPozycjeSchematuIdTab = value
        End Set
    End Property

    Public Property ZleceniaTab() As DataTable
        Get
            Return mvarZleceniaTab
        End Get
        Set(ByVal value As DataTable)
            mvarZleceniaTab = value
        End Set
    End Property

    Public Property WyznDrut() As Boolean
        Get
            Return mvarWyznDrut
        End Get
        Set(ByVal value As Boolean)
            mvarWyznDrut = value
        End Set
    End Property

    Public Property WagaDrutu() As Double
        Get
            Return mvarWagaDrutu
        End Get
        Set(ByVal value As Double)
            mvarWagaDrutu = value
        End Set
    End Property

    Public Property DrutTab() As DataTable
        Get
            Return mvarDrutTab
        End Get
        Set(ByVal value As DataTable)
            mvarDrutTab = value
        End Set
    End Property
#End Region

#Region "Methods"

    Public Sub New()
        mvarTrZlecenia = New Collection(Of clsInhTrZlecenie)
        UtworzTabele()
        mvarTrSchemat = New Collection(Of clsInhTrSchemat)
        mvarTrPozycjaSchematu = New Collection(Of clsInhTrPozycjeSchematu)
        mvarTrDrut = New Collection(Of clsInhTrDrut)
        mvarTrMaterialProdukcyjny = New Collection(Of clsInhTrMaterialProdukcyjny)
    End Sub

    Private Sub UtworzTabele()

        With mvarSchematTab.Columns
            .Add("klient", GetType(String))
            .Add("schemat", GetType(Integer))
            .Add("termin", GetType(String))
            .Add("pozycja_zlecenia_id", GetType(String))
        End With

        With mvarPozycjeSchematuIdTab.Columns
            .Add("id", GetType(Integer))
        End With

        With mvarZleceniaTab.Columns
            .Add("suma", GetType(String))
            .Add("numer", GetType(Integer))
            .Add("klient", GetType(String))
            .Add("data_wysylki", GetType(String))
        End With

        With mvarDrutTab.Columns
            .Add("id", GetType(Integer))
        End With

    End Sub

    Public Sub SzukajZlecenia(ByVal lvarDbHolder As clsDataBase, ByVal Zapytanie As String, Wyzn As Boolean)

        Dim lvarZapytanie As String

        lvarZapytanie = Zapytanie

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 1, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie)
                lvarReader = .ExecuteQuery
            End With

            If lvarReader.HasRows Then
                While lvarReader.Read
                    mvarTrZlecenia.Add(New clsInhTrZlecenie(CInt(IIf(IsDBNull(lvarReader.Item("id")), -1, lvarReader.Item("id"))), lvarDbHolder, Wyzn))
                End While
            End If

        End If

    End Sub

    Public Sub SzukajSchematu(ByVal lvarDbHolder As clsDataBase, ByVal Zapytanie As String)

        Dim lvarZapytanie As String
        Dim lvarNewRow As DataRow
        lvarZapytanie = Zapytanie

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 2, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie)
                lvarReader = .ExecuteQuery
            End With

            If lvarReader.HasRows Then
                While lvarReader.Read

                    lvarNewRow = mvarSchematTab.NewRow()
                    With lvarNewRow
                        .Item("klient") = IIf(IsDBNull(lvarReader.Item("klient")), Nothing, lvarReader.Item("klient"))
                        .Item("schemat") = IIf(IsDBNull(lvarReader.Item("schemat_id")), Nothing, lvarReader.Item("schemat_id"))
                        .Item("termin") = IIf(IsDBNull(lvarReader.Item("termin")), Nothing, lvarReader.Item("termin"))
                        .Item("pozycja_zlecenia_id") = IIf(IsDBNull(lvarReader.Item("pozycje_zlecenia_id")), Nothing, lvarReader.Item("pozycje_zlecenia_id"))
                    End With
                    mvarSchematTab.Rows.Add(lvarNewRow)
                    mvarTrSchemat.Add(New clsInhTrSchemat(CInt(IIf(IsDBNull(lvarReader.Item("schemat_id")), Nothing, lvarReader.Item("schemat_id"))), lvarDbHolder))
                End While
            End If
        End If

    End Sub

    Public Sub SzukajPozycjiSchematu(ByVal lvarDbHolder As clsDataBase, ByVal Zapytanie As String)

        Dim lvarZapytanie As String
        lvarZapytanie = Zapytanie

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 3, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie)
                lvarReader = .ExecuteQuery
            End With

            If lvarReader.HasRows Then
                While lvarReader.Read
                    If CInt(IIf(IsDBNull(lvarReader.Item("opcja_pozycji_schematu_id")), Nothing, lvarReader.Item("opcja_pozycji_schematu_id"))) <> 1 Then
                        mvarTrPozycjaSchematu.Add(New clsInhTrPozycjeSchematu(CInt(IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))), lvarDbHolder))
                    End If
                End While
            End If
        End If

    End Sub

    Public Sub SzukajDrutuRodzica1(ByVal lvarDbHolder As clsDataBase, ByVal SchematId As String)

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then

            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 5, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", SchematId)
                lvarReader = .ExecuteQuery
            End With

            If lvarReader.HasRows Then
                While lvarReader.Read
                    mvarTrMaterialProdukcyjny.Add(New clsInhTrMaterialProdukcyjny(CInt(IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))), lvarDbHolder))
                End While
            End If
        End If

    End Sub

    Public Sub SzukajDrutuRodzica2(ByVal lvarDbHolder As clsDataBase, ByVal PozycjaSchematuId As String, Waga As Boolean)

        Dim lvarZapytanie As String
        lvarZapytanie = PozycjaSchematuId
        Dim lvarI As Integer = 0

        Dim lvarReader As OdbcDataReader = Nothing
        Dim lvarReader1 As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 4, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", PozycjaSchematuId)
                lvarReader = .ExecuteQuery
            End With
            If lvarReader.HasRows Then
                While lvarReader.Read
                    If Not Waga Then
                        mvarTrDrut.Add(New clsInhTrDrut(CInt(IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))), lvarDbHolder))
                    Else
                        lvarI = lvarI + 1
                        lvarSqlRunner = New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 15, VAR_ERROR_HANDLER)
                        With lvarSqlRunner
                            .AddParameter("p_zapytanie", lvarReader.Item("id").ToString())
                            lvarReader1 = .ExecuteQuery
                        End With
                        If lvarReader.HasRows Then
                            While lvarReader1.Read
                                Dim foo As Double = 0
                                If Double.TryParse(lvarReader1.Item("waga").ToString().Trim(), foo) Then
                                    mvarWagaDrutu = mvarWagaDrutu + foo
                                End If
                            End While
                        End If
                    End If
                End While
            End If

        End If

    End Sub

    Public Sub SzukajIdPozycjiSchematu(ByVal lvarDbHolder As clsDataBase, PozycjaSchematuId As Integer, SchematId As Integer)

        Dim lvarReader As OdbcDataReader = Nothing
        Dim lvarNewRow As DataRow

        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 7, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", SchematId.ToString())
                lvarReader = .ExecuteQuery
            End With
            While lvarReader.Read

                lvarNewRow = mvarPozycjeSchematuIdTab.NewRow
                With lvarNewRow
                    .Item("id") = IIf(IsDBNull(lvarReader.Item("id")), -1, lvarReader.Item("id"))
                End With
                mvarPozycjeSchematuIdTab.Rows.Add(lvarNewRow)
            End While
        End If

    End Sub

    Shared Function SzukajMaterialuProdukcyjnegoPotomka(ByVal lvarDbHolder As clsDataBase, ByVal id As String) As Integer

        Dim lvarId As String
        Dim lvarWyzn As Boolean

        lvarId = id

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then

            Do
                Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 8, VAR_ERROR_HANDLER)
                'SELECT drut_rodzic_id, wyzn_material_produkcyjny FROM tracking.rodzic WHERE drut_id =<%p_zapytanie%>;
                With lvarSqlRunner
                    .AddParameter("p_zapytanie", lvarId)
                    lvarReader = .ExecuteQuery
                End With

                If lvarReader.HasRows Then
                    While lvarReader.Read
                        lvarId = CStr(IIf(IsDBNull(lvarReader.Item("drut_rodzic_id")), String.Empty, lvarReader.Item("drut_rodzic_id")))
                        lvarWyzn = CBool(IIf(IsDBNull(lvarReader.Item("wyzn_material_produkcyjny")), String.Empty, lvarReader.Item("wyzn_material_produkcyjny")))
                    End While
                End If
            Loop While (Not lvarWyzn)
        End If

        Return CInt(lvarId)
    End Function

    Public Sub PracownikSuma(ByVal lvarDbHolder As clsDataBase, ByVal Zapytanie As String)

        Dim lvarZapytanie As String
        Dim lvarNewRow As DataRow
        lvarZapytanie = Zapytanie

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 9, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie)
                lvarReader = .ExecuteQuery
            End With

            If lvarReader.HasRows Then
                While lvarReader.Read

                    lvarNewRow = mvarZleceniaTab.NewRow()
                    With lvarNewRow
                        .Item("suma") = IIf(IsDBNull(lvarReader.Item("suma")), Nothing, lvarReader.Item("suma"))
                        .Item("numer") = IIf(IsDBNull(lvarReader.Item("numer")), Nothing, lvarReader.Item("numer"))
                        .Item("klient") = IIf(IsDBNull(lvarReader.Item("nazwa_skrocona")), Nothing, lvarReader.Item("nazwa_skrocona"))
                        .Item("data_wysylki") = IIf(IsDBNull(lvarReader.Item("data_wysylki")), Nothing, lvarReader.Item("data_wysylki"))
                    End With
                    mvarZleceniaTab.Rows.Add(lvarNewRow)
                End While
            End If
        End If

    End Sub

    Public Sub SzukajDrutuDoKontroli(lvarDbHolder As clsDataBase, OpcjaId As Integer, PozycjaZleceniaId As Integer, Wyzn As Boolean)

        Dim lvarReader As OdbcDataReader = Nothing

        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 10, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_opcja_id", OpcjaId.ToString())
                .AddParameter("p_pozycje_zlecenia_id", PozycjaZleceniaId.ToString())
                lvarReader = .ExecuteQuery
            End With

            While lvarReader.Read
                If Wyzn Then
                    mvarTrDrut.Add(New clsInhTrDrut(CInt(IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))), lvarDbHolder))
                Else
                    mvarWyznDrut = True
                End If
            End While


        End If

    End Sub

    Public Sub SzukajDrutuPoKontroli(lvarDbHolder As clsDataBase, OpcjaId As Integer, PozycjaZleceniaId As Integer, Opcja2Id As Integer, PracownikId As Integer)

        Dim lvarReader As OdbcDataReader = Nothing
        Dim lvarZapytanie As String

        If PozycjaZleceniaId <> 0 Then
            lvarZapytanie = " AND schemat.pozycje_zlecenia_id = " & PozycjaZleceniaId
        End If
        If PracownikId <> 0 Then
            lvarZapytanie = lvarZapytanie & " AND drut.pracownik_id = " & PracownikId
        End If


        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 11, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_opcja_id", OpcjaId.ToString())
                .AddParameter("p_opcja2_id", Opcja2Id.ToString())
                .AddParameter("p_zapytanie", lvarZapytanie)
                lvarReader = .ExecuteQuery
            End With
            While lvarReader.Read
                mvarTrDrut.Add(New clsInhTrDrut(CInt(IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))), lvarDbHolder))
            End While
        End If

    End Sub

    Shared Function SzukajIdSchematu(lvarDbHolder As clsDataBase, id As String) As Integer

        Dim lvarId As String

        lvarId = id

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then

            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 12, VAR_ERROR_HANDLER)
            With lvarSqlRunner
                .AddParameter("p_zapytanie", lvarId)
                lvarReader = .ExecuteQuery
            End With

            If lvarReader.HasRows Then
                While lvarReader.Read
                    lvarId = IIf(IsDBNull(lvarReader.Item("schemat_id")), -1, lvarReader.Item("schemat_id")).ToString()
                End While
            End If

        End If

        Return CInt(lvarId)

    End Function

    Shared Function SzukajIdPozycjiZlecenia(lvarDbHolder As clsDataBase, IdDrutu As String) As Integer

        Dim lvarId As String

        lvarId = IdDrutu

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then

            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 13, VAR_ERROR_HANDLER)
            With lvarSqlRunner
                .AddParameter("p_zapytanie", lvarId)
                lvarReader = .ExecuteQuery
            End With

            If lvarReader.HasRows Then
                While lvarReader.Read
                    lvarId = IIf(IsDBNull(lvarReader.Item("pozycje_zlecenia_id")), -1, lvarReader.Item("pozycje_zlecenia_id")).ToString()
                End While
            End If

        End If

        Return CInt(lvarId)

    End Function

    Shared Function SzukajIdDrutu(lvarDbHolder As clsDataBase, IdDrutu As String) As String

        Dim lvarId As String = String.Empty

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then

            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 14, VAR_ERROR_HANDLER)
            With lvarSqlRunner
                .AddParameter("p_id", IdDrutu)
                lvarReader = .ExecuteQuery
            End With

            If lvarReader.HasRows Then
                While lvarReader.Read
                    lvarId = IIf(IsDBNull(lvarReader.Item("id")), -1, lvarReader.Item("id")).ToString()
                End While
            End If

        End If

        Return lvarId

    End Function


    Public Sub SzukajDrutuDoKontroliId(lvarDbHolder As clsDataBase, OpcjaId As Integer, PozycjaZleceniaId As Integer)

        Dim lvarReader As OdbcDataReader = Nothing
        Dim lvarNewRow As DataRow

        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_tracking", "cls_szukanie", 10, VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_opcja_id", OpcjaId.ToString())
                .AddParameter("p_pozycje_zlecenia_id", PozycjaZleceniaId.ToString())
                lvarReader = .ExecuteQuery
            End With

            While lvarReader.Read
                lvarNewRow = mvarDrutTab.NewRow()
                With lvarNewRow
                    .Item("id") = IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))
                End With
                mvarDrutTab.Rows.Add(lvarNewRow)
            End While

        End If

    End Sub

#End Region

End Class
